﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;
using CimeaSoft.BE;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;

namespace CimeaSoft.Repository
{
    public class CimeaSoftDatabaseInitializer:DropCreateDatabaseAlways<CIMEAContext>
    {
        protected override void Seed(CIMEAContext context)
        {
            if (context == null)
                throw new ArgumentException("context");

            CargaCuenca();
            CargaHidrologia();
            CargaTramo();
            CargaLecho();
            CargaCuenca2();
            CargaHidrologia2();
            CargaTramo2();
            CargaLecho2();
            CargaIncluRapidos();
            CargaFrecRapidos();
            CargaCompSubstrato();
            CargaRegVelocidad();
            CargaPorCause();
            CargaElementos();
            CargaCobVegetacion();
            CargaGradCubierta();
            CargaEstrCubierta();
            CargaCalCubierta();
            CargaGradNaturalidad();
            CargaGradCubierta2();
            CargaCalCubierta2();
            CargaGradNaturalidad2();
            CargaAbi();
        }

        public void CargaCuenca()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `Cuenca$`.Apartado, `Cuenca$`.Cuenca, `Cuenca$`.Poco, `Cuenca$`.ChkP, `Cuenca$`.Medio, `Cuenca$`.ChkM, `Cuenca$`.Mucho, `Cuenca$`.ChkMu FROM `Cuenca$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Apartado", "Apartado");
                        bulkCopy.ColumnMappings.Add("Cuenca", "Cuenca");
                        bulkCopy.ColumnMappings.Add("Poco", "Poco");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.ColumnMappings.Add("Medio", "Medio");
                        bulkCopy.ColumnMappings.Add("ChkM", "ChkM");
                        bulkCopy.ColumnMappings.Add("Mucho", "Mucho");
                        bulkCopy.ColumnMappings.Add("ChkMu", "ChkMu");
                        bulkCopy.DestinationTableName = "Cuenca";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaHidrologia()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `Hidrologia$`.Apartado, `Hidrologia$`.Hidrologia, `Hidrologia$`.Poco, `Hidrologia$`.ChkP, `Hidrologia$`.Medio, `Hidrologia$`.ChkM, `Hidrologia$`.Mucho, `Hidrologia$`.ChkMu FROM `Hidrologia$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Apartado", "Apartado");
                        bulkCopy.ColumnMappings.Add("Hidrologia", "Hidrologia");
                        bulkCopy.ColumnMappings.Add("Poco", "Poco");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.ColumnMappings.Add("Medio", "Medio");
                        bulkCopy.ColumnMappings.Add("ChkM", "ChkM");
                        bulkCopy.ColumnMappings.Add("Mucho", "Mucho");
                        bulkCopy.ColumnMappings.Add("ChkMu", "ChkMu");
                        bulkCopy.DestinationTableName = "Hidrologia";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaTramo()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `Tramo$`.Apartado, `Tramo$`.Tramo, `Tramo$`.Poco, `Tramo$`.ChkP, `Tramo$`.Medio, `Tramo$`.ChkM, `Tramo$`.Mucho, `Tramo$`.ChkMu FROM `Tramo$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Apartado", "Apartado");
                        bulkCopy.ColumnMappings.Add("Tramo", "Tramo");
                        bulkCopy.ColumnMappings.Add("Poco", "Poco");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.ColumnMappings.Add("Medio", "Medio");
                        bulkCopy.ColumnMappings.Add("ChkM", "ChkM");
                        bulkCopy.ColumnMappings.Add("Mucho", "Mucho");
                        bulkCopy.ColumnMappings.Add("ChkMu", "ChkMu");
                        bulkCopy.DestinationTableName = "Tramo";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaLecho()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `Lecho$`.Apartado, `Lecho$`.Lecho, `Lecho$`.Poco, `Lecho$`.ChkP, `Lecho$`.Medio, `Lecho$`.ChkM, `Lecho$`.Mucho, `Lecho$`.ChkMu FROM `Lecho$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Apartado", "Apartado");
                        bulkCopy.ColumnMappings.Add("Lecho", "Lecho");
                        bulkCopy.ColumnMappings.Add("Poco", "Poco");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.ColumnMappings.Add("Medio", "Medio");
                        bulkCopy.ColumnMappings.Add("ChkM", "ChkM");
                        bulkCopy.ColumnMappings.Add("Mucho", "Mucho");
                        bulkCopy.ColumnMappings.Add("ChkMu", "ChkMu");
                        bulkCopy.DestinationTableName = "Lecho";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaCuenca2()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `Cuenca2$`.Apartado, `Cuenca2$`.Cuenca, `Cuenca2$`.Poco, `Cuenca2$`.ChkP, `Cuenca2$`.Medio, `Cuenca2$`.ChkM, `Cuenca2$`.Mucho, `Cuenca2$`.ChkMu FROM `Cuenca2$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Apartado", "Apartado");
                        bulkCopy.ColumnMappings.Add("Cuenca", "Cuenca");
                        bulkCopy.ColumnMappings.Add("Poco", "Poco");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.ColumnMappings.Add("Medio", "Medio");
                        bulkCopy.ColumnMappings.Add("ChkM", "ChkM");
                        bulkCopy.ColumnMappings.Add("Mucho", "Mucho");
                        bulkCopy.ColumnMappings.Add("ChkMu", "ChkMu");
                        bulkCopy.DestinationTableName = "Cuenca2";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaHidrologia2()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `Hidrologia2$`.Apartado, `Hidrologia2$`.Hidrologia, `Hidrologia2$`.Poco, `Hidrologia2$`.ChkP, `Hidrologia2$`.Medio, `Hidrologia2$`.ChkM, `Hidrologia2$`.Mucho, `Hidrologia2$`.ChkMu FROM `Hidrologia2$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Apartado", "Apartado");
                        bulkCopy.ColumnMappings.Add("Hidrologia", "Hidrologia");
                        bulkCopy.ColumnMappings.Add("Poco", "Poco");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.ColumnMappings.Add("Medio", "Medio");
                        bulkCopy.ColumnMappings.Add("ChkM", "ChkM");
                        bulkCopy.ColumnMappings.Add("Mucho", "Mucho");
                        bulkCopy.ColumnMappings.Add("ChkMu", "ChkMu");
                        bulkCopy.DestinationTableName = "Hidrologia2";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaTramo2()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `Tramo2$`.Apartado, `Tramo2$`.Tramo, `Tramo2$`.Poco, `Tramo2$`.ChkP, `Tramo2$`.Medio, `Tramo2$`.ChkM, `Tramo2$`.Mucho, `Tramo2$`.ChkMu FROM `Tramo2$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Apartado", "Apartado");
                        bulkCopy.ColumnMappings.Add("Tramo", "Tramo");
                        bulkCopy.ColumnMappings.Add("Poco", "Poco");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.ColumnMappings.Add("Medio", "Medio");
                        bulkCopy.ColumnMappings.Add("ChkM", "ChkM");
                        bulkCopy.ColumnMappings.Add("Mucho", "Mucho");
                        bulkCopy.ColumnMappings.Add("ChkMu", "ChkMu");
                        bulkCopy.DestinationTableName = "Tramo2";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaLecho2()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `Lecho2$`.Apartado, `Lecho2$`.Lecho, `Lecho2$`.Poco, `Lecho2$`.ChkP, `Lecho2$`.Medio, `Lecho2$`.ChkM, `Lecho2$`.Mucho, `Lecho2$`.ChkMu FROM `Lecho2$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Apartado", "Apartado");
                        bulkCopy.ColumnMappings.Add("Lecho", "Lecho");
                        bulkCopy.ColumnMappings.Add("Poco", "Poco");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.ColumnMappings.Add("Medio", "Medio");
                        bulkCopy.ColumnMappings.Add("ChkM", "ChkM");
                        bulkCopy.ColumnMappings.Add("Mucho", "Mucho");
                        bulkCopy.ColumnMappings.Add("ChkMu", "ChkMu");
                        bulkCopy.DestinationTableName = "Lecho2";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaIncluRapidos()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `IncluRapidos$`.Familia, `IncluRapidos$`.Descripcion, `IncluRapidos$`.Puntuacion, `IncluRapidos$`.ChkP FROM `IncluRapidos$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Familia", "Familia");
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "IncluRapidos";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaFrecRapidos()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `FrecRapidos$`.Descripcion, `FrecRapidos$`.Puntuacion, `FrecRapidos$`.ChkP FROM `FrecRapidos$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "FrecuRapidos";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaCompSubstrato()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `CompSubstrato$`.Familia, `CompSubstrato$`.Descripcion, `CompSubstrato$`.Puntuacion, `CompSubstrato$`.ChkP FROM `CompSubstrato$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Familia", "Familia");
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "CompSubstrato";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaRegVelocidad()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `RegVelocidad$`.Familia, `RegVelocidad$`.Descripcion, `RegVelocidad$`.Puntuacion, `RegVelocidad$`.ChkP FROM `RegVelocidad$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Familia", "Familia");
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "RegimVelocidad";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaPorCause()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `PorCause$`.Descripcion, `PorCause$`.Puntuacion, `PorCause$`.ChkP FROM `PorCause$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "PorcCause";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaElementos()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `Elementos$`.Familia, `Elementos$`.Descripcion, `Elementos$`.Puntuacion, `Elementos$`.ChkP FROM `Elementos$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Familia", "Familia");
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "Elementos";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaCobVegetacion()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `CobVegetacion$`.Familia, `CobVegetacion$`.Descripcion, `CobVegetacion$`.Puntuacion, `CobVegetacion$`.ChkP FROM `CobVegetacion$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Familia", "Familia");
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "CobVegetacion";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaGradCubierta()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `GradCubierta$`.Descripcion, `GradCubierta$`.Puntuacion, `GradCubierta$`.ChkP FROM `GradCubierta$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "GradCubierta";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaEstrCubierta()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `EstrCubierta$`.Descripcion, `EstrCubierta$`.Puntuacion, `EstrCubierta$`.ChkP FROM `EstrCubierta$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "EstruCubierta";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaCalCubierta()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `CalCubierta$`.Descripcion, `CalCubierta$`.Puntuacion, `CalCubierta$`.ChkP FROM `CalCubierta$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "CalidCubierta";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaGradNaturalidad()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `GradNaturalidad$`.Descripcion, `GradNaturalidad$`.Puntuacion, `GradNaturalidad$`.ChkP FROM `GradNaturalidad$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "GradoNaturalidad";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaGradCubierta2()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `GradCubierta2$`.Descripcion, `GradCubierta2$`.Puntuacion, `GradCubierta2$`.ChkP FROM `GradCubierta2$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "GradCubierta2";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaCalCubierta2()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `CalCubierta2$`.Descripcion, `CalCubierta2$`.Puntuacion, `CalCubierta2$`.ChkP FROM `CalCubierta2$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "CalidCubierta2";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaGradNaturalidad2()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `GradNaturalidad2$`.Descripcion, `GradNaturalidad2$`.Puntuacion, `GradNaturalidad2$`.ChkP FROM `GradNaturalidad2$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Descripcion", "Descripcion");
                        bulkCopy.ColumnMappings.Add("Puntuacion", "Puntuacion");
                        bulkCopy.ColumnMappings.Add("ChkP", "ChkP");
                        bulkCopy.DestinationTableName = "GradoNaturalidad2";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }

        public void CargaAbi()
        {
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='D:\CIMEA_SOFT\Datos.xls'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; //Office 2003-2007
            //string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Data.xlsx'; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"; // Office 2010-2012

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("SELECT `Abi$`.Orden, `Abi$`.Familia, `Abi$`.ABI, `Abi$`.Abundancia FROM `Abi$`", connection);

                connection.Open();

                // Create DbDataReader to Data Worksheet
                using (DbDataReader dr = command.ExecuteReader())
                {
                    // SQL Server Connection String
                    string sqlConnectionString = "Data Source=.;Initial Catalog=CIMEA;Integrated Security=True";

                    // Bulk Copy to SQL Server
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.TableLock))
                    {
                        bulkCopy.ColumnMappings.Add("Orden", "Orden");
                        bulkCopy.ColumnMappings.Add("Familia", "Familia");
                        bulkCopy.ColumnMappings.Add("ABI", "ABI");
                        bulkCopy.ColumnMappings.Add("Abundancia", "Abundancia");
                        bulkCopy.DestinationTableName = "Abi";
                        bulkCopy.WriteToServer(dr);
                    }
                }
            }
        }
    }
}
